package br.com.maps.contacts.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import br.com.maps.contacts.Contact;
import br.com.maps.contacts.Contact.ContactsColumns;
import br.com.maps.contacts.ContactsBuilder;
import br.com.maps.contacts.ExtensionLine;
import br.com.maps.contacts.ExtensionLine.ExtensionLineColumns;

/**
 * DAO para contatos
 * 
 * @author Bruno Ortiz
 * 
 */
public class ContactsDAO {

	protected static final String CATEGORIA = "contato";

	protected static final String NOME_BANCO = "maps_ramais";

	protected static final String NOME_TABELA_CONTATOS = "contatos";

	protected static final String NOME_TABELA_ASSOC = "assoc_contatos_ramais";

	protected static final String NOME_TABELA_RAMAIS = "ramais";

	protected SQLiteDatabase db;

	public ContactsDAO(Context ctx) {
		this.db = ctx.openOrCreateDatabase(NOME_BANCO, Context.MODE_PRIVATE, null);
	}

	protected ContactsDAO() {
	}

	public long save(Contact contact) {
		long id = contact.getId();
		if (id != 0) {
			this.update(contact);
		} else {
			id = this.insert(contact);
		}
		return id;
	}

	private long insert(Contact contact) {
		ContentValues values = new ContentValues();
		values.put(ContactsColumns.NOME, contact.getName());
		values.put(ContactsColumns.EMAIL, contact.getMapsEmail());
		values.put(ContactsColumns.FAVORITO, contact.isFavorite());
		this.db.beginTransaction();
		long id = -1;
		try {
			id = this.db.insertOrThrow(NOME_TABELA_CONTATOS, "", values);
			this.insertContactRamal(contact, id);
			this.db.setTransactionSuccessful();
		} catch (SQLException e) {
			Log.e(CATEGORIA, "Erro ao inserir contato");
		} finally {
			this.db.endTransaction();
		}
		return id;
	}

	private void insertContactRamal(Contact contact, long id) throws SQLException {
		ContentValues ramalValue;
		ContentValues assocValue;
		for (ExtensionLine ramal : contact.getRamais()) {
			ramalValue = new ContentValues();
			assocValue = new ContentValues();
			ramalValue.put(ExtensionLineColumns.ExtensionLine, ramal.getValue());
			long idRamal = this.db.insertOrThrow(NOME_TABELA_RAMAIS, "", ramalValue);
			assocValue.put("ramal_id", idRamal);
			assocValue.put("contact_id", id);
			this.db.insertOrThrow(NOME_TABELA_ASSOC, "", assocValue);
		}
	}

	private long update(Contact contact) {
		ContentValues values = new ContentValues();
		values.put(ContactsColumns.NOME, contact.getName());
		values.put(ContactsColumns.EMAIL, contact.getMapsEmail());
		values.put(ContactsColumns.FAVORITO, contact.isFavorite());
		String _id = String.valueOf(contact.getId());
		String where = ContactsColumns._ID + "=" + _id;
		int rowCount = this.db.update(NOME_TABELA_CONTATOS, values, where, null);
		return rowCount;
	}

	/**
	 * @return the cursor with all the contacts
	 */
	public Cursor getContactsCursor() {
		try {
			return this.db.query(NOME_TABELA_CONTATOS, new String[] { "*" }, null, null, null, null, null, null);
		} catch (SQLException e) {
			Log.e(CATEGORIA, "Erro ao buscar contatos: " + e.toString());
			return null;
		}
	}

	/**
	 * Find all the contacts
	 * 
	 * @return A list with all the contacts
	 */
	public List<Contact> findAll() {
		Cursor c = this.getContactsCursor();
		List<Contact> contacts = new ArrayList<Contact>(50);
		if (c.moveToFirst()) {
			int indexId = c.getColumnIndex(ContactsColumns._ID);
			int indexName = c.getColumnIndex(ContactsColumns.NOME);
			int indexEmail = c.getColumnIndex(ContactsColumns.EMAIL);
			int indexFavourite = c.getColumnIndex(ContactsColumns.FAVORITO);
			do {
				long contactId = c.getLong(indexId);
				String name = c.getString(indexName);
				String email = c.getString(indexEmail);
				boolean favourite = c.getInt(indexFavourite) > 0;
				List<ExtensionLine> extensionLines = this.findExtensionLinesByContactID(contactId);
				Contact contact = ContactsBuilder.createAContact().withID(contactId).withName(name).withMapsEmail(email).withFavourite(favourite)
						.withExtensionLine(extensionLines).buildContact();
				contacts.add(contact);
			} while (c.moveToNext());
		}
		c.close();
		return contacts;
	}

	public List<ExtensionLine> findExtensionLinesByContactID(long contactId) {
		List<ExtensionLine> extensions = null;
		List<Long> extensionLineIds = this.findExtensionLineIdsByContactId(contactId);
		if (!extensionLineIds.isEmpty()) {
			extensions = new ArrayList<ExtensionLine>();
			for (long extensionLineId : extensionLineIds) {
				Cursor c = this.db.query(NOME_TABELA_RAMAIS, new String[] { "*" }, ExtensionLineColumns._ID + "='" + extensionLineId + "'", null,
						null, null, null);
				if (c.moveToFirst()) {
					int indexId = c.getColumnIndex(ExtensionLineColumns._ID);
					int indexExtension = c.getColumnIndex(ExtensionLineColumns.ExtensionLine);
					do {
						long extensionId = c.getLong(indexId);
						String extensionValue = c.getString(indexExtension);
						extensions.add(new ExtensionLine(extensionId, extensionValue));
					} while (c.moveToNext());
				}
				c.close();
			}
		}
		return extensions;
	}

	private List<Long> findExtensionLineIdsByContactId(long contactId) {
		Cursor c = this.db.query(NOME_TABELA_ASSOC, new String[] { "*" }, "contact_id='" + contactId + "'", null, null, null, null);
		List<Long> extensionLineIds = new ArrayList<Long>();
		if (c.moveToFirst()) {
			int indexExtensionId = c.getColumnIndex("ramal_id");
			do {
				long extensionId = c.getLong(indexExtensionId);
				extensionLineIds.add(extensionId);
			} while (c.moveToNext());
		}
		c.close();
		return extensionLineIds;
	}

	public void close() {
		if (this.db != null) {
			this.db.close();
		}
	}
}
